<?php

class Statistics extends CI_Model{
    function __construct(){
        parent::__construct();
    }
    
    function getIngredientsOnRange($fromDate, $toDate){
/*
1. Pull all meals allocated on this range
2. Get all meal ingredients, count ingredients
3. Show ingredients, order by count
4. Also show the meals with the ingredients as a separate list (order by date)
5. Show the average amount of votes per night allocated
*/
        //Get the meals on the range
        $this->db->from('mealtimes as MT');
        $this->db->where('mealtime_date >=', $fromDate);
        $this->db->where('mealtime_date <=', $toDate);
        
        //Join on the tables to get the ingredients
        $this->db->join('mealtime_meals as MTM','MT.mealtime_id = MTM.mealtime_id');
        $this->db->join('meals as M','MTM.meal_id = M.meal_id');
        $this->db->join('meal_ingredients as MI','MI.meal_id = M.meal_id');
        $this->db->join('ingredients as I','MI.ingredient_id = I.ingredient_id');
        
        //Count the ingredients
        $this->db->select("I.ingredient_title, I.ingredient_id, COUNT(I.ingredient_id) ingredient_count");
        $this->db->group_by("I.ingredient_id");
        $this->db->order_by("ingredient_count","desc");
        
        //Get the result
        return $this->db->get();
    }
    
    function getMealVotesOnRange($fromDate, $toDate){
/*
1. Pull all meals allocated on this range
2. Get all meal ingredients, count ingredients
3. Show ingredients, order by count
4. Also show the meals with the ingredients as a separate list (order by date)
5. Show the average amount of votes per night allocated
*/
        //Get the meals on the range
        $this->db->from('mealtimes as MT');
        $this->db->where('mealtime_date >=', $fromDate);
        $this->db->where('mealtime_date <=', $toDate);
        $this->db->join('mealtime_meals as MTM','MT.mealtime_id = MTM.mealtime_id');
        $this->db->join('meals as M','M.meal_id = MTM.meal_id');
        $this->db->select('MTM.meal_id, M.meal_title, UNIX_TIMESTAMP(MT.mealtime_date) as date');
        $this->db->order_by('date','asc');
        $this->db->group_by("MTM.meal_id");
        
        $query = $this->db->get();
        $mealList = array();
        $resturnList = array();
        foreach($query->result() as $result){
            $mealList[] = $result->meal_id;
            $returnList[$result->meal_id]['title'] = $result->meal_title;
            $returnList[$result->meal_id]['vote_count'] = 0;
            $returnList[$result->meal_id]['mealtime_count'] = 0;
            $returnList[$result->meal_id]['ratio'] = "n/a";
            $returnList[$result->meal_id]['date'] = $result->date;
            $returnList[$result->meal_id]['ingredients'] = "";
        }
        //Find the meals that have votes
        $this->db->from('mealtime_meals as MTM');
        $this->db->join('user_votes as UV','MTM.mealtime_meals_id = UV.mealtime_meals_id');
        
        $this->db->where_in('MTM.meal_id', $mealList);
        
        //Count the Votes
        $this->db->select("COUNT(MTM.meal_id) as vote_count, MTM.meal_id");
        $this->db->group_by("MTM.meal_id");
        
        //Get the result
        $query = $this->db->get();
        foreach($query->result() as $result){
            $returnList[$result->meal_id]['vote_count'] = $result->vote_count;
        }
        //Get the ingredients in the meals
        $this->db->from("meal_ingredients as MI");
        $this->db->join('ingredients as I', 'MI.ingredient_id = I.ingredient_id');
        $this->db->order_by('MI.meal_id');
        $this->db->select("MI.meal_id, I.ingredient_title");
        $this->db->where_in("MI.meal_id",$mealList);
        $query = $this->db->get();
        
        foreach($query->result() as $result){
            $returnList[$result->meal_id]['ingredients'] .= $result->ingredient_title.", ";
        }
        
        //Find how many times the meal has been on
        $this->db->from('mealtime_meals as MTM');
        $this->db->where_in('MTM.meal_id', $mealList);
        $this->db->join('mealtimes as MT','MT.mealtime_id = MTM.mealtime_id');
        $this->db->group_by("MTM.meal_id");
        
        //Don't count future meals which haven't been voted on yet
        $this->db->where("MT.mealtime_date <", date("Y-m-d"));
        
        $this->db->select('MTM.meal_id, COUNT(MTM.meal_id) as mealtime_count');
        
        $query = $this->db->get();
        foreach($query->result() as $result){
            $returnList[$result->meal_id]['mealtime_count'] = $result->mealtime_count;
            if ($result->mealtime_count != 0){
                $returnList[$result->meal_id]['ratio'] = 
                        $returnList[$result->meal_id]['vote_count'] /  $result->mealtime_count;
            }
        }
        
        return $returnList;
    }
    
    
    
    
}

